package com.yunhe.sms.dao;

import com.yunhe.sms.mode.Teacher;
import com.yunhe.sms.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {

    /**
     *查找一个讲师
     * @param no
     * @return
     */
    public static Teacher getOneTeacher(int no){
        Connection conn = JdbcUtil.getConn();
        String sqlQuery =" select *from teacher where no =?";
        PreparedStatement stmt = JdbcUtil.getstmt(conn, sqlQuery, no);
        ResultSet resultSet = JdbcUtil.sqlQuery(stmt);
        Teacher teacher = null;
        if (null!=resultSet){
            try {
                while (resultSet.next()){
                    int no1 = resultSet.getInt("no");
                    String name = resultSet.getString("name");
                    String age = resultSet.getString("age");
                    String sex = resultSet.getString("sex");
                    String phone = resultSet.getString("phone");
                    teacher = new Teacher(no1,name,age,sex,phone);
                    break;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        JdbcUtil.close(conn,stmt,resultSet);
        return teacher;
    }

    /**
     *查找讲师列表
     * @return
     */
    public static List<Teacher> getAllTeacher(){
        Connection conn = JdbcUtil.getConn();
        String sqlQuery =" select *from teacher order by no asc";
        PreparedStatement stmt = JdbcUtil.getstmt(conn, sqlQuery);
        ResultSet resultSet = JdbcUtil.sqlQuery(stmt);
        Teacher teacher = null;
        ArrayList<Teacher> teachers = new ArrayList<>();
        if (null!=resultSet){
            try {
                while (resultSet.next()){
                    int no1 = resultSet.getInt("no");
                    String name = resultSet.getString("name");
                    String age = resultSet.getString("age");
                    String sex = resultSet.getString("sex");
                    String phone = resultSet.getString("phone");
                    teacher = new Teacher(no1,name,age,sex,phone);
                    teachers.add(teacher);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        JdbcUtil.close(conn,stmt,resultSet);
        return teachers;
    }

    /**
     * 添加讲师
     * @param teacher
     */
    public static void teacherAdd(Teacher teacher){
        if(null!=teacher){
            Connection conn = JdbcUtil.getConn();
            String sqlInsert ="insert into teacher(no,name,age,sex,phone) values(?,?,?,?,?) ";
            PreparedStatement stmt = JdbcUtil.getstmt(conn, sqlInsert, teacher.getId(), teacher.getName(), teacher.getAge(), teacher.getSex(), teacher.getPhone());
            JdbcUtil.sqlUpdate(stmt);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }

    /**
     * 修改讲师
     * @param teacher
     */
    public static void teacherUpdate(Teacher teacher){
        if(null!=teacher){
            Connection conn = JdbcUtil.getConn();
            String sqlInsert ="update teacher set name =?, age =?, sex =?, phone =? where no =? ";
            PreparedStatement stmt = JdbcUtil.getstmt(conn, sqlInsert, teacher.getName(), teacher.getAge(), teacher.getSex(), teacher.getPhone(), teacher.getId());
            JdbcUtil.sqlUpdate(stmt);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }

    /**
     * 删除讲师
     * @param no
     */
    public static void teacherDel(int no){
        Connection conn = JdbcUtil.getConn();
        String sql = " delete from teacher where no =?";
        PreparedStatement stmt = JdbcUtil.getstmt(conn, sql,no);
        JdbcUtil.sqlUpdate(stmt);
        JdbcUtil.close(stmt);
        JdbcUtil.close(conn);
    }
}
